Skip Headers

Oracle® Database Application Developer's Guide - Large Objects 10g Release 2 (10.2) Part Number B14249-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 10 PL/SQL Semantics for LOBs

This chapter contains these topics:

- PL/SQL Statements and Variables

- Implicit Conversions Between CLOB and VARCHAR2

- Explicit Conversion Functions

- PL/SQL CLOB Comparison Rules

## PL/SQL Statements and Variables

In PL/SQL, a number of semantic changes have been made as described in the previous paragraphs.

Note: The following discussions, concerning CLOBs and VARCHAR2s, also apply to BLOBs and RAWs, unless otherwise noted. In the text, BLOB and RAW are not explicitly mentioned.

PL/SQL semantics support is described in the following sections:

- Implicit Conversions Between CLOB and VARCHAR2

- Explicit Conversion Functions

- VARCHAR2 and CLOB in PL/SQL Built-In Functions

- PL/SQL CLOB Comparison Rules

## Implicit Conversions Between CLOB and VARCHAR2

Implicit conversions from `CLOB` to `VARCHAR2` and from `VARCHAR2` to `CLOB` datatypes are allowed in PL/SQL. These conversions enable you to perform the following operations in your application:

- `CLOB` columns can be selected into `VARCHAR2` PL/SQL variables

- `VARCHAR2` columns can be selected into `CLOB` variables

- Assignment and parameter passing between `CLOB`s and `VARCHAR2`s

Accessing a CLOB as a VARCHAR2 in PL/SQL

The following example illustrates the way `CLOB` data is accessed when the `CLOB`s are treated as `VARCHAR2`s:

    declare
       myStoryBuf VARCHAR2(4001);
    BEGIN
       SELECT ad_sourcetext INTO myStoryBuf FROM print_media WHERE ad_id = 12001;
       -- Display Story by printing myStoryBuf directly
    END;
    /

Assigning a CLOB to a VARCHAR2 in PL/SQL

    declare
    myLOB CLOB;
    BEGIN
    SELECT 'ABCDE' INTO myLOB FROM print_media WHERE ad_id = 11001;
    -- myLOB is a temporary LOB.
    -- Use myLOB as a lob locator
      DBMS_OUTPUT.PUT_LINE('Is temp? '||DBMS_LOB.ISTEMPORARY(myLOB));
    END;
    /

## Explicit Conversion Functions

In SQL and PL/SQL, the following explicit conversion functions convert other data types to and from `CLOB`, `NCLOB`, and `BLOB` as part of the LONG-to-LOB migration:

- `TO_CLOB()`: Converting from `VARCHAR2`, `NVARCHAR2`, or `NCLOB` to a `CLOB`

- `TO_NCLOB()`: Converting from `VARCHAR2`, `NVARCHAR2`, or `CLOB` to an `NCLOB`

- `TO_BLOB()`: Converting from `RAW` to a `BLOB`

- `TO_CHAR()` converts a `CLOB` to a `CHAR` type. When you use this function to convert a character LOB into the database character set, if the LOB value to be converted is larger than the target type, then the database returns an error. Implicit conversions also raise an error if the LOB data does not fit.

- `TO_NCHAR()` converts an `NCLOB` to an NCHAR type. When you use this function to convert a character LOB into the national character set, if the LOB value to be converted is larger than the target type, then the database returns an error. Implicit conversions also raise an error if the LOB data does not fit.

- `CAST` does not directly support any of the LOB datatypes. When you use `CAST` to convert a `CLOB` value into a character datatype, an `NCLOB` value into a national character datatype, or a `BLOB` value into a `RAW` datatype, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target datatype. If the resulting value is larger than the target type, then the database returns an error.

Other explicit conversion functions are not supported, such as, `TO_NUMBER()`, see Table 9-1, "SQL VARCHAR2 Functions and Operators on LOBs". Conversion function details are explained in Chapter 11, "Migrating Columns from LONGs to LOBs".

### VARCHAR2 and CLOB in PL/SQL Built-In Functions

`CLOB` and `VARCHAR2` are still two distinct types. But depending on the usage, a CLOB can be passed to SQL and PL/SQL `VARCHAR2` built-in functions, used exactly like a `VARCHAR2`. Or the variable can be passed into `DBMS_LOB` APIs, acting like a LOB locator. Please see the following combined example,"CLOB Variables in PL/SQL".

PL/SQL `VARCHAR2` functions and operators can take `CLOB`s as arguments or operands.

When the size of a `VARCHAR2` variable is not large enough to contain the result from a function that returns a `CLOB`, or a `SELECT` on a `CLOB` column, an error should be raised and no operation will be performed. This is consistent with `VARCHAR2` semantics.

CLOB Variables in PL/SQL

    1 declare
    2   myStory CLOB;
    3   revisedStory CLOB;
    4   myGist VARCHAR2(100);
    5   revisedGist VARCHAR2(100);
    6 BEGIN
    7  -- select a CLOB column into a CLOB variable
    8  SELECT Story INTO myStory FROM print_media WHERE product_id=10;
    9  -- perform VARCHAR2 operations on a CLOB variable
    10 revisedStory := UPPER(SUBSTR(myStory, 100, 1));
    11 -- revisedStory is a temporary LOB
    12 -- Concat a VARCHAR2 at the end of a CLOB
    13 revisedStory := revisedStory || myGist;
    14 -- The following statement will raise an error because myStory is
    15 -- longer than 100 bytes
    16 myGist := myStory;
    17 END;

Please note that in line 10 of "CLOB Variables in PL/SQL", a temporary `CLOB` is implicitly created and is pointed to by the `revisedStory` `CLOB` locator. In the current interface the line can be expanded as:

    buffer VARCHAR2(32000)
    DBMS_LOB.CREATETEMPORARY(revisedStory);
    buffer := UPPER(DBMS_LOB.SUBSTR(myStory,100,1));
    DBMS_LOB.WRITE(revisedStory,length(buffer),1, buffer);

In line 13, myGist is appended to the end of the temporary LOB, which has the same effect of:

    DBMS_LOB.WRITEAPPEND(revisedStory, myGist, length(myGist));

In some occasions, implicitly created temporary LOBs in PL/SQL statements can change the representation of LOB locators previously defined. Consider the next example.

Change in Locator-Data Linkage

    1 declare
    2 myStory CLOB;
    3 amt number:=100;
    4 buffer VARCHAR2(100):='some data';
    5 BEGIN
    6 -- select a CLOB column into a CLOB variable
    7 SELECT Story INTO myStory FROM print_media WHERE product_id=10;
    8 DBMS_LOB.WRITE(myStory, amt, 1, buf);
    9 -- write to the persistent LOB in the table
    10
    11 myStory:= UPPER(SUBSTR(myStory, 100, 1));
    12 -- perform VARCHAR2 operations on a CLOB variable, temporary LOB created.
    13 -- Changes will not be reflected in the database table from this point on.
    14
    15 update print_media set Story = myStory WHERE product_id = 10;
    16 -- an update is necessary to synchronize the data in the table.
    17 END;

After line 7, `myStory` represents a persistent LOB in `print_media`.

The `DBMS_LOB.WRITE` call in line 8 directly writes the data to the table.

No `UPDATE` statement is necessary. Subsequently in line 11, a temporary LOB is created and assigned to `myStory` because `myStory` is now used like a local `VARCHAR2` variable. The LOB locator `myStory` now points to the newly-created temporary LOB.

Therefore, modifications to `myStory` will no longer be reflected in the database. To propagate the changes to the database table, an `UPDATE` statement becomes necessary now. Note again that for the previous persistent LOB, the `UPDATE` is not required.

Temporary LOBs created in a program block as a result of a `SELECT` or an assignment are freed automatically at the end of the PL/SQL block/function/procedure. You can choose to free the temporary LOBs to reclaim system resources and temporary tablespace by calling `DBMS_LOB.FREETEMPORARY` on the `CLOB` variable.

Freeing Temporary LOBs Automatically and Manually

    declare
       Story1 CLOB;
       Story2 CLOB;
       StoryCombined CLOB;
       StoryLower CLOB;
    BEGIN
       SELECT Story INTO Story1 FROM print_media WHERE product_ID = 1;
       SELECT Story INTO Story2 FROM print_media WHERE product_ID = 2;
       StoryCombined := Story1 || Story2; -- StoryCombined is a temporary LOB
       -- Free the StoryCombined manually to free up space taken
       DBMS_LOB.FREETEMPORARY(StoryCombined);
       StoryLower := LOWER(Story1) || LOWER(Story2);
    END; -- At the end of block, StoryLower is freed.

## PL/SQL CLOB Comparison Rules

Like `VARCHAR2`s, when a `CLOB` is compared with another `CLOB` or compared with a `VARCHAR2`, a set of rules determines the comparison. The rules are usually called a "collating sequence". In Oracle, `CHAR`s and `VARCHAR2`s have slightly different sequences due to the blank padding of `CHAR`s.

### CLOBs Follow the VARCHAR2 Collating Sequence

As a rule, `CLOB`s follow the same collating sequence as `VARCHAR2`s. That is, when a `CLOB` is compared, the result is consistent with if the `CLOB` data content is retrieved into a `VARCHAR2` buffer and the `VARCHAR2` is compared. The rule applies to all cases including comparisons between `CLOB` and `CLOB`, `CLOB` and `VARCHAR2`, and `CLOB` and `CHAR`.

Note: When a `CLOB` is compared with a `CHAR` string, it is always the character data of the `CLOB` being compared with the string. Likewise, when two `CLOB`s are compared, the data content of the two `CLOB`s are compared, not their LOB locators.

It makes no sense to compare `CLOB`s with non-character data, or with `BLOB`s. An error is returned in these cases.